package com.neusoft.neuiotms.dcom.service.impl;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.EntityNotFoundException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.google.common.collect.Lists;
import com.neusoft.bizcore.auth.common.bean.UserBean;
import com.neusoft.bizcore.web.exception.UnitedException;
import com.neusoft.bizcore.web.support.Searchable;
import com.neusoft.bizcore.web.utils.EasyPoiStylerColorImpl;
import com.neusoft.neuiotms.dcom.bean.ProjectUserBean;
import com.neusoft.neuiotms.dcom.constants.DCOMConstants;
import com.neusoft.neuiotms.dcom.converter.QuestionnaireFeedbackConverter;
import com.neusoft.neuiotms.dcom.dto.QuestionnaireFeedbackDTO;
import com.neusoft.neuiotms.dcom.model.OperationGroup;
import com.neusoft.neuiotms.dcom.model.QuestionnaireFeedback;
import com.neusoft.neuiotms.dcom.model.enums.QuestionnaireScope;
import com.neusoft.neuiotms.dcom.model.enums.QuestionnaireType;
import com.neusoft.neuiotms.dcom.repository.OperationGroupRepository;
import com.neusoft.neuiotms.dcom.repository.QuestionnaireFeedbackRepository;
import com.neusoft.neuiotms.dcom.repository.QuestionnaireRepository;
import com.neusoft.neuiotms.dcom.service.InstitutionsService;
import com.neusoft.neuiotms.dcom.service.QuestionnaireFeedbackService;
import com.neusoft.neuiotms.dcom.util.PermUtils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.word.WordExportUtil;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class QuestionnaireFeedbackServiceImpl implements QuestionnaireFeedbackService {

    @Autowired
    private QuestionnaireRepository questionnaireRepository;
    @Autowired
    private QuestionnaireFeedbackRepository questionnaireFeedbackRepository;
    @Autowired
    private QuestionnaireFeedbackConverter questionnaireFeedbackConverter;
    @Autowired
    private InstitutionsService institutionsService;
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private OperationGroupRepository operationGroupRepository;

    @Override
    public List<QuestionnaireFeedbackDTO> index(final Searchable searchable, final UserBean user) {
        final ProjectUserBean info = this.institutionsService.getMyInstitutions(user);
        List<QuestionnaireFeedback> list = null;
        if (user.getRoles().stream().filter(it -> DCOMConstants.ROLE_WORKER.equals(it.getRole())).count() > 0) {
            searchable.put("username", user.getUsername());
            list = this.questionnaireFeedbackRepository.index(searchable);
        } else if (user.getRoles().stream().filter(it -> DCOMConstants.ROLE_WORKER_GROUP_LEADER.equals(it.getRole()))
                .count() > 0) {
            final List<String> usernameArray = Lists.newArrayList();
            info.getInstitutions().forEach(inst -> {
                inst.getResidents().stream().map(resi -> resi.getUsername()).forEach(usernameArray::add);
                inst.getUsers().stream().map(u -> u.getUsername()).forEach(usernameArray::add);
            });
            searchable.put("usernameArray", usernameArray);
            list = this.questionnaireFeedbackRepository.index(searchable);
        } else {
            list = Lists.newArrayList();
        }
        return this.questionnaireFeedbackConverter.toListDTO(list);
    }

    @Override
    public Page<QuestionnaireFeedbackDTO> search(final Pageable pageable, final Searchable searchable,
            final UserBean user) {
        Page<QuestionnaireFeedback> page = null;
        if (user.getRoles().stream()
                .filter(it -> DCOMConstants.ROLE_WORKER.equals(it.getRole())
                        || DCOMConstants.ROLE_WORKER_GROUP_LEADER.equals(it.getRole())
                        || DCOMConstants.ROLE_ZONGJIAN.equals(it.getRole()))
                .findAny().isPresent()) {
            searchable.put("username", user.getUsername());
            page = this.questionnaireFeedbackRepository.search(pageable, searchable);
        } else if (user.getRoles().stream()
                .filter(it -> DCOMConstants.ROLE_ADMIN.equals(it.getRole()))
                .findAny().isPresent()) {
            page = this.questionnaireFeedbackRepository.search(pageable, searchable);
        } else {
            page = new PageImpl<>(Lists.newArrayList(), pageable, 0);
        }
        return this.questionnaireFeedbackConverter.toPageDTO(page);
    }

    @Override
    public QuestionnaireFeedbackDTO create(final QuestionnaireFeedbackDTO dto) {
        QuestionnaireFeedback model = this.questionnaireFeedbackConverter.toModel(dto);
        model = this.questionnaireFeedbackRepository.save(model);
        return this.questionnaireFeedbackConverter.toDTO(model);
    }

    @Override
    public QuestionnaireFeedbackDTO update(final Long id, final QuestionnaireFeedbackDTO dto) {
        QuestionnaireFeedback model = this.questionnaireFeedbackConverter.toModel(dto);
        model = this.questionnaireFeedbackRepository.save(model);
        return this.questionnaireFeedbackConverter.toDTO(model);
    }

    @Override
    public QuestionnaireFeedbackDTO details(final Long id, final UserBean userBean) {
        QuestionnaireFeedback model = null;
        try {
            model = this.questionnaireFeedbackRepository.getOne(id);
        } catch (final EntityNotFoundException e) {
            throw new UnitedException("实体不存在");
        }

        final QuestionnaireFeedbackDTO dto = this.questionnaireFeedbackConverter.toDTO(model);
        this.beforeMerge(userBean, dto);
        return dto;
    }

    @Override
    public QuestionnaireFeedbackDTO detailsByTask(final Long taskId, final UserBean userBean) {
        final QuestionnaireFeedback model =
                this.questionnaireFeedbackRepository.findByUsernameAndQuestionnaire_Id(userBean.getUsername(), taskId);
        if (null == model) {
            return null;
        }
        final QuestionnaireFeedbackDTO dto = this.questionnaireFeedbackConverter.toDTO(model);
        this.beforeMerge(userBean, dto);
        return dto;
    }

    private void beforeMerge(final UserBean userBean, final QuestionnaireFeedbackDTO dto) {
        if (PermUtils.isWorkerGroupLeader(userBean)) {

            List<QuestionnaireFeedbackDTO> items = Lists.newArrayList();
            // 导自己的报告,需要抽取小组下一线运维的报告
            if (dto.getQuestionnaire().getScope().equals(QuestionnaireScope.LEADER)) {
                final Searchable searchable = new Searchable();
                searchable.put("year", dto.getQuestionnaire().getYear());
                searchable.put("scope", QuestionnaireScope.WORKER);
                searchable.put("type", dto.getQuestionnaire().getType());
                items = this.index(searchable, userBean);
                this.merge(dto, items);
            }
        } else if (PermUtils.isZongjian(userBean)) {
            List<QuestionnaireFeedbackDTO> items = Lists.newArrayList();
            // 导自己的报告,需要抽取小组下一线运维的报告
            if (dto.getUsername().equals(userBean.getUsername())) {
                final Searchable searchable = new Searchable();
                searchable.put("year", dto.getQuestionnaire().getYear());
                searchable.put("scope", QuestionnaireScope.WORKER);
                searchable.put("type", dto.getQuestionnaire().getType());
                searchable.put("submitted", true);
                final List<QuestionnaireFeedback> feedbacks = this.questionnaireFeedbackRepository.index(searchable);
                items = this.questionnaireFeedbackConverter.toListDTO(feedbacks);
            } else {
                final Searchable searchable = new Searchable();
                final List<String> usernameArray = Lists.newArrayList();
                // 提取运维组长负责的所有厅局，进而获得厅局下所有的驻场人员
                final OperationGroup group = this.operationGroupRepository.findByGroupLeaderCode(dto.getUsername());
                group.getInstitutions().forEach(inst -> {
                    inst.getResidents().stream().map(resi -> resi.getUsername()).forEach(usernameArray::add);
                    inst.getUsers().stream().map(u -> u.getUsername()).forEach(usernameArray::add);
                });
                searchable.put("usernameArray", usernameArray);
                searchable.put("year", dto.getQuestionnaire().getYear());
                searchable.put("scope", QuestionnaireScope.WORKER);
                searchable.put("type", dto.getQuestionnaire().getType());
                searchable.put("submitted", true);
                final List<QuestionnaireFeedback> feedbacks = this.questionnaireFeedbackRepository.index(searchable);
                items = this.questionnaireFeedbackConverter.toListDTO(feedbacks);
            }
            this.merge(dto, items);
        }
    }

    private void merge(final QuestionnaireFeedbackDTO myFeedback,
            final List<QuestionnaireFeedbackDTO> items) {

        if ((null != items) && (items.size() > 0)) {
            for (final QuestionnaireFeedbackDTO item : items) {
                myFeedback.setStatsRoutineWork(myFeedback.getStatsRoutineWork() + item.getStatsRoutineWork());
                myFeedback.setStatsEnvWork(myFeedback.getStatsEnvWork() + item.getStatsEnvWork());
                myFeedback.setStatsNetworkWork(myFeedback.getStatsNetworkWork() + item.getStatsNetworkWork());
                myFeedback.setStatsSecurityWork(myFeedback.getStatsSecurityWork() + item.getStatsSecurityWork());
                myFeedback.setStatsServerWork(myFeedback.getStatsServerWork() + item.getStatsServerWork());
                myFeedback.setStatsStorageWork(myFeedback.getStatsStorageWork() + item.getStatsStorageWork());
                myFeedback.setStatsAssetWork(myFeedback.getStatsAssetWork() + item.getStatsAssetWork());
                myFeedback.setStatsDocumentWork(myFeedback.getStatsDocumentWork() + item.getStatsDocumentWork());
                myFeedback.setStatsInsuranceWork(myFeedback.getStatsInsuranceWork() + item.getStatsInsuranceWork());
                myFeedback.setStatsAssistWork(myFeedback.getStatsAssistWork() + item.getStatsAssistWork());
                myFeedback.setStatsIncrementWork(myFeedback.getStatsIncrementWork() + item.getStatsIncrementWork());
            }
        }
    }

    @Override
    public void delete(final Long id) {
        this.questionnaireRepository.deleteById(id);

    }

    @Override
    public boolean submit(final Long id) {
        QuestionnaireFeedback model = null;
        try {
            model = this.questionnaireFeedbackRepository.getOne(id);
        } catch (final EntityNotFoundException e) {
            throw new UnitedException("实体不存在");
        }

        model.setSubmitted(true);
        model.setSubmitAt(new Date());
        this.questionnaireFeedbackRepository.save(model);

        return true;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Page<Map<String, Object>> report(Pageable pageable, final String groupCode, final String username,
            final String type,
            final Date start,
            final Date end) {

        final String countSql = this.buildTotalSql(groupCode, username, type, start, end);
        QuestionnaireFeedbackServiceImpl.log.info(countSql);
        final Query query1 = this.entityManager.createNativeQuery(countSql);
        query1.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result1 = query1.getResultList();
        int total = 0;
        if (result1.size() > 0) {
            total = Integer.parseInt(String.valueOf(result1.get(0).get("count")));
        }

        final String sql = this.buildQuerySql(pageable, groupCode, username, type, start, end);
        QuestionnaireFeedbackServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return new PageImpl<>(result, pageable, total);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Workbook exportReport(final String groupCode, final String username, final String type, final Date start,
            final Date end) {
        final String sql = this.buildExportSql(groupCode, username, type, start, end);
        QuestionnaireFeedbackServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();

        final List<ReportVO> list = new ArrayList<>(result.size() == 0 ? 1 : result.size());
        result.stream().map(t -> {
            final ReportVO vo = new ReportVO();
            vo.setUser(t.get("user"));
            vo.setType(t.get("type"));
            vo.setTotal_times(t.get("total_times"));
            return vo;
        }).forEach(list::add);

        final ExportParams param =
                new ExportParams("工作总结统计", "工作总结统计", ExcelType.XSSF);
        param.setStyle(EasyPoiStylerColorImpl.class);
        return ExcelExportUtil.exportExcel(param, ReportVO.class, list);
    }

    private String buildTotalSql(final String groupCode, final String username, final String type, final Date start,
            final Date end) {
        final String statement = " count(*) as count ";
        return this.buildSql(groupCode, username, type, start, end, statement);
    }

    private String buildQuerySql(Pageable pageable, final String groupCode, final String username, final String type,
            final Date start,
            final Date end) {
        final String statement = " * ";
        String noPageSql = this.buildSql(groupCode, username, type, start, end, statement);
        noPageSql += " limit " + pageable.getPageSize() + " offset " + pageable.getOffset();

        return noPageSql;
    }

    private String buildExportSql(final String groupCode, final String username, final String type, final Date start,
            final Date end) {
        final String statement = " * ";
        return this.buildSql(groupCode, username, type, start, end, statement);
    }

    private String buildSql(final String groupCode, final String username, final String type, final Date start,
            final Date end, String statement) {
        String where = "where 1=1 ";

        if (StringUtils.isNotBlank(groupCode)) {
            where += "and t2.group_code='" + groupCode + "' ";
        }

        if (StringUtils.isNotBlank(username)) {
            where += "and t1.username='" + username + "' ";
        }
        if (StringUtils.isNotBlank(type)) {
            where += "and t2.type='" + type + "' ";
        }
        if (null != start) {
            where += "and t1.submit_at>='" + DateFormatUtils.format(start, "yyyy-MM-dd") + "' ";
        }
        if (null != end) {
            final Calendar c = Calendar.getInstance();
            c.setTime(end);
            c.add(Calendar.DAY_OF_MONTH, 1);
            where += "and t1.submit_at<'" + DateFormatUtils.format(c.getTime(), "yyyy-MM-dd") + "' ";
        }
        String sql = "SELECT "
                + statement
                + " FROM ("
                + "SELECT "
                + " user, "
                + " username, "
                + " CASE type "
                + " WHEN 'END_YEAR_SUMMARY' THEN "
                + "     '年末总结' "
                + " WHEN 'MIDDLE_YEAR_SUMMARY' THEN "
                + "     '年中总结' "
                + " END AS type, "
                + " count(*) AS total_times "
                + "FROM "
                + " hainan_questionnaire_feedback t1 "
                + "LEFT JOIN hainan_questionnaire t2 ON t1.questionnaire_id = t2.id "
                + "%s "
                + "GROUP BY "
                + " t1. USER, "
                + " t1.username, "
                + " t2.type "
                + ") t";

        sql = String.format(sql, where, where, where, where);

        return sql;
    }

    @Setter
    @Getter
    static class ReportVO {

        @Excel(name = "人员", orderNum = "0", width = 30)
        private Object user;

        @Excel(name = "类别", orderNum = "1", width = 30)
        private Object type;

        @Excel(name = "填报次数", orderNum = "2", width = 30)
        private Object total_times;

    }

    @Override
    public boolean rollback(final Long id) {
        QuestionnaireFeedback model = null;
        try {
            model = this.questionnaireFeedbackRepository.getOne(id);
        } catch (final EntityNotFoundException e) {
            throw new UnitedException("实体不存在");
        }

        model.setSubmitted(false);
        model.setSubmitAt(null);
        this.questionnaireFeedbackRepository.save(model);

        return true;
    }

    @Override
    public XWPFDocument export(final Long id, final UserBean userBean) {
        if (id == null) {
            throw new UnitedException("导出工作总结报告出错，必须选择一条数据");
        }
        String tplName = "word/summary.docx";
        Map<String, Object> map = new HashedMap<>();
        final QuestionnaireFeedbackDTO dto = this.details(id, userBean);
        map = this.convert(dto, null);
        if (PermUtils.isWorkerGroupLeader(userBean)) {
            if (dto.getQuestionnaire().getScope().equals(QuestionnaireScope.LEADER)) {
                tplName = "word/summary_leader.docx";
            }
        } else if (PermUtils.isZongjian(userBean)) {
            tplName = "word/summary_leader.docx";
        }

        try {
            final XWPFDocument doc = WordExportUtil.exportWord07(
                    tplName, map);
            return doc;
        } catch (final Exception e) {
            QuestionnaireFeedbackServiceImpl.log.error("", e);
            throw new UnitedException("导出工作总结报告出错");
        }
    }

    private Map<String, Object> convert(final QuestionnaireFeedbackDTO myFeedback,
            final List<QuestionnaireFeedbackDTO> items) {
        final Map<String, Object> map = new HashMap<>();
        map.put("institutionName", myFeedback.getInstitutionName());
        map.put("submitAt", DateFormatUtils.format(myFeedback.getSubmitAt(), "yyyy-MM-dd"));
        map.put("user", myFeedback.getUser());
        map.put("groupName", myFeedback.getGroupName());
        map.put("preface", myFeedback.getPreface());

        if (QuestionnaireType.MIDDLE_YEAR_SUMMARY.equals(myFeedback.getQuestionnaire().getType())) {
            map.put("type", "年中");
        } else {
            map.put("type", "年终");
        }

        if ((null != items) && (items.size() > 0)) {
            for (final QuestionnaireFeedbackDTO item : items) {
                myFeedback.setStatsRoutineWork(myFeedback.getStatsRoutineWork() + item.getStatsRoutineWork());
                myFeedback.setStatsEnvWork(myFeedback.getStatsEnvWork() + item.getStatsEnvWork());
                myFeedback.setStatsNetworkWork(myFeedback.getStatsNetworkWork() + item.getStatsNetworkWork());
                myFeedback.setStatsSecurityWork(myFeedback.getStatsSecurityWork() + item.getStatsSecurityWork());
                myFeedback.setStatsServerWork(myFeedback.getStatsServerWork() + item.getStatsServerWork());
                myFeedback.setStatsStorageWork(myFeedback.getStatsStorageWork() + item.getStatsStorageWork());
                myFeedback.setStatsAssetWork(myFeedback.getStatsAssetWork() + item.getStatsAssetWork());
                myFeedback.setStatsDocumentWork(myFeedback.getStatsDocumentWork() + item.getStatsDocumentWork());
                myFeedback.setStatsInsuranceWork(myFeedback.getStatsInsuranceWork() + item.getStatsInsuranceWork());
                myFeedback.setStatsAssistWork(myFeedback.getStatsAssistWork() + item.getStatsAssistWork());
                myFeedback.setStatsIncrementWork(myFeedback.getStatsIncrementWork() + item.getStatsIncrementWork());
            }
        }
        map.put("statsRoutineWork", myFeedback.getStatsRoutineWork());
        map.put("statsEnvWork", myFeedback.getStatsEnvWork());
        map.put("statsNetworkWork", myFeedback.getStatsNetworkWork());
        map.put("statsSecurityWork", myFeedback.getStatsSecurityWork());
        map.put("statsServerWork", myFeedback.getStatsServerWork());
        map.put("statsStorageWork", myFeedback.getStatsStorageWork());
        map.put("statsAssetWork", myFeedback.getStatsAssetWork());
        map.put("statsDocumentWork", myFeedback.getStatsDocumentWork());
        map.put("statsInsuranceWork", myFeedback.getStatsInsuranceWork());
        map.put("statsAssistWork", myFeedback.getStatsAssistWork());
        map.put("statsIncrementWork", myFeedback.getStatsIncrementWork());

        double totalTimes = myFeedback.getStatsRoutineWork() + myFeedback.getStatsEnvWork()
                + myFeedback.getStatsNetworkWork()
                + myFeedback.getStatsSecurityWork() + myFeedback.getStatsServerWork() +
                myFeedback.getStatsStorageWork() + myFeedback.getStatsAssetWork() + myFeedback.getStatsDocumentWork()
                + myFeedback.getStatsInsuranceWork() + myFeedback.getStatsAssistWork()
                + myFeedback.getStatsIncrementWork() + 0.0;

        if (totalTimes == 0) {
            totalTimes = 1.0;
        }
        final DecimalFormat df = new DecimalFormat("##.0%");
        map.put("statsRoutineWorkPercent", df.format(myFeedback.getStatsRoutineWork() / totalTimes));
        map.put("statsEnvWorkPercent", df.format(myFeedback.getStatsEnvWork() / totalTimes));
        map.put("statsNetworkWorkPercent", df.format(myFeedback.getStatsNetworkWork() / totalTimes));
        map.put("statsSecurityWorkPercent", df.format(myFeedback.getStatsSecurityWork() / totalTimes));
        map.put("statsServerWorkPercent", df.format(myFeedback.getStatsServerWork() / totalTimes));
        map.put("statsStorageWorkPercent", df.format(myFeedback.getStatsStorageWork() / totalTimes));
        map.put("statsAssetWorkPercent", df.format(myFeedback.getStatsAssetWork() / totalTimes));
        map.put("statsDocumentWorkPercent", df.format(myFeedback.getStatsDocumentWork() / totalTimes));
        map.put("statsInsuranceWorkPercent", df.format(myFeedback.getStatsInsuranceWork() / totalTimes));
        map.put("statsAssistWorkPercent", df.format(myFeedback.getStatsAssistWork() / totalTimes));
        map.put("statsIncrementWorkPercent", df.format(myFeedback.getStatsIncrementWork() / totalTimes));
        map.put("excellence", myFeedback.getExcellence());
        map.put("experience", myFeedback.getExperience());
        map.put("target", myFeedback.getTarget());
        map.put("content", myFeedback.getContent());

        return map;
    }

}
